package net.phoenix.repository.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import net.phoenix.repository.data.NodePropertyData;

/**
 * 
 * @author lixf
 * 
 */
public abstract class ReferenceValueDao extends JdbcDao {


	// 取得所有r_%的表名 by mali 2010-8-12
	private static final String SQL_REFERENCE_TABLES = "SELECT TNAME FROM TAB WHERE TABTYPE='TABLE' AND TNAME LIKE 'R/_%' ESCAPE '/' ";

	protected List<String> getReferenceTableName() throws SQLException {
		PreparedStatement command = null;
		ResultSet rs = null;
		List<String> tabs = new ArrayList<String>();
		try {
			command = connection.prepareStatement(SQL_REFERENCE_TABLES);
			rs = command.executeQuery();
			while (rs.next()) {
				tabs.add(rs.getString(1));
			}
		} finally {
			this.close(command, rs);
		}
		return tabs;
	}

	// 构建查询语句 like 'select * from tab1 union all select * from tab2' by mali
	// 2010-8-12
	protected String makeSelQuery(String value) throws SQLException {
		StringBuffer query = new StringBuffer();
		String query1 = "select node_id from ";
		String query2 = " where value = '" + value + "'";
		List<String> tabs = getReferenceTableName();
		for (int i = 0; i < tabs.size(); i++) {
			query.append(query1).append(tabs.get(i)).append(query2);
			if (i != (tabs.size() - 1))
				query = query.append(" union all ");
		}
		return query.toString();
	}

	// 构建查询语句 like 'select * from tab1 union all select * from tab2' by mali
	// 2010-8-12
	protected String makeSelQuery(String name, String value) throws SQLException {
		StringBuffer query = new StringBuffer();
		String query1 = "select node_id from ";
		String query2 = " where value = '" + value + "'";
		List<String> tabs = getReferenceTableName();
		for (int i = 0; i < tabs.size(); i++) {
			query.append(query1).append(tabs.get(i)).append(query2);
			if (i != (tabs.size() - 1))
				query = query.append(" union all ");
		}
		return query.toString();
	}

	public int countReferenceByNode(String nodeID) throws SQLException {
		String SQL_REFERENCE_COUNT_BY_Content = "select count(*) from (" + this.makeSelQuery(nodeID) + ")";
		PreparedStatement command = null;
		try {
			command = connection.prepareStatement(SQL_REFERENCE_COUNT_BY_Content);
			int result = this.queryInt(command);
			return result;
		} finally {
			this.close(command);
		}
	}

	public abstract NodePropertyData[] findReferenceByNode(String nodeID, int fromIndex, int pageSize) throws SQLException ;
	/**
	 * @Todo 支持根据Name来过滤
	 * @param nodeID
	 * @param name
	 * @param fromIndex
	 * @param pageSize
	 * @return
	 * @throws SQLException
	 */
	public abstract NodePropertyData[] findReferenceByNode(String nodeID, String name, int fromIndex, int pageSize) throws SQLException ;

	public NodePropertyData[] findReferenceByNodeId(String nodeID) throws SQLException {
		PreparedStatement command = null;
		try {
			command = connection.prepareStatement(this.makeSelQuery(nodeID));
			NodePropertyData[] result = this.retrieve(command);
			return result;
		} finally {
			this.close(command);
		}
	}

	protected NodePropertyData[] retrieve(PreparedStatement statement) throws SQLException {
		ResultSet rs = null;
		try {
			rs = statement.executeQuery();
			ArrayList<NodePropertyData> result = new ArrayList<NodePropertyData>();
			while (rs.next()) {
				NodePropertyData group = new NodePropertyData();
				;
				this.populate(rs, group);
				result.add(group);
			}
			return (NodePropertyData[]) result.toArray(new NodePropertyData[result.size()]);
		} finally {
			rs.close();
			statement.close();
		}
	}

	private void populate(ResultSet rs, NodePropertyData data) throws SQLException {
		data.setID(rs.getString("NODE_ID"));
		// data.setName(rs.getString("PROPERTY_ID"));
	}
}
